﻿// DataProviders/PostgreSqlProvider.cs

using System.Collections.Generic;
using System.Linq;
using SqlSugar;
using DatabaseManager.Core.Models;

namespace DatabaseManager.DataProviders
{
    public class PostgreSqlProvider : IDataProvider
    {
        private readonly ISqlSugarClient _db;

        public PostgreSqlProvider(ISqlSugarClient db)
        {
            _db = db;
        }

        public bool TestConnection() => _db.Ado.GetScalar("SELECT 1") != null;

        public List<TableInfo> GetTables()
        {
            var sql = @"
                SELECT table_name AS Name, table_schema AS Schema, obj_description(format('%s.%s', table_schema, table_name)::regclass, 'pg_class') AS Description
                FROM information_schema.tables
                WHERE table_schema NOT IN ('pg_catalog', 'information_schema')";

            return _db.Ado.SqlQuery<TableInfo>(sql);
        }

        public List<ColumnInfo> GetColumns(string tableName)
        {
            var sql = $@"
                SELECT 
                    column_name AS Name,
                    data_type AS DataType,
                    is_nullable::boolean AS IsNullable,
                    column_default AS DefaultValue,
                    col_description(format('%s.%s', table_name, column_name)::regclass, column_name::int) AS Description,
                    ordinal_position AS OrdinalPosition,
                    (SELECT COUNT(*) FROM pg_index WHERE pg_index.indrelid = format('%s.%s', table_name, column_name)::regclass AND pg_index.indkey[0] = ordinal_position::int) > 0 AS IsPrimaryKey
                FROM information_schema.columns
                WHERE table_name = '{tableName}'";

            return _db.Ado.SqlQuery<ColumnInfo>(sql);
        }

        public List<string> GetDatabases()
        {
            var sql = "SELECT datname FROM pg_database WHERE datistemplate = false";
            return _db.Ado.SqlQuery<string>(sql).ToList();
        }
    }
}